package com.xinyirun.scm.core.system.mapper.business.pp;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.xinyirun.scm.bean.entity.business.pp.BPpEntity;
import com.xinyirun.scm.bean.system.vo.business.pp.BPpVo;
import com.xinyirun.scm.common.constant.DictConstant;
import com.xinyirun.scm.common.constant.SystemConstants;
import com.xinyirun.scm.core.system.config.mybatis.typehandlers.JsonArrayTypeHandler;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * <p>
 * 生产计划表 Mapper 接口
 * </p>
 *
 * @author xinyirun
 * @since 2024-04-18
 */
@Repository
public interface BPpMapper extends BaseMapper<BPpEntity> {


    String comm_select = ""
            +"SELECT                                                                                                                                    "
            +"	t.id,                                                                                                                                   "
            +"	t.status,                                                                                                                               "
            +"	t.CODE,                                                                                                                                 "
            +"	t1.label status_name,                                                                                                                   "
            +"	t6.code release_order_code,	                                                                                                            "
            +"	t11.code router_code,					                                                                                                "
            +"	t11.name router_name,					                                                                                                "
            +"	t11.id router_id,					                                                                                                    "
            +"	t14.json_product_list,	                                                                                                                "
            +"	t15.json_material_list,	                                                                                                                "
            +"	t10.remark,					                                                                                                            "
            +"	t2.name c_name,                                                                                                                         "
            +"	t.c_time,			                                                                                                                    "
            +"	t3.name u_name,			                                                                                                                "
            +"	t.u_time,				                                                                                                                "
            +"	t4.name audit_name,		                                                                                                                "
            +"	t.audit_time,	   		                                                                                                                "
            +"	t.owner_code,	   		                                                                                                                "
            +"	t.owner_id,	   		                                                                                                                    "
            +"	t5.name owner_name,	   		                                                                                                            "
            +"	t.release_order_id,	   		                                                                                                            "
            +"	t.plan_time,	   		                                                                                                                "
            +"	ifnull(t13.bwo_sum, 0) bwo_sum,	   		                                                                                                "
            +"	t9.warehouse_name	   		                                                                                                "
            +"FROM                                                                                                                                      "
            +"	b_pp t                                                                                                                                  "
            +"	LEFT JOIN s_dict_data t1 ON t.STATUS = t1.dict_value AND t1.CODE = '"+ DictConstant.DICT_B_PP_STATUS+"'                                 "
            +"	LEFT JOIN m_staff t2 ON t2.id = t.c_id                                                                                                  "
            +"	LEFT JOIN m_staff t3 ON t3.id = t.u_id                                                                                                  "
            +"	LEFT JOIN m_staff t4 ON t4.id = t.audit_id                                                                                              "
            +"	LEFT JOIN m_owner t5 ON t5.id = t.owner_id                                                                                              "
            +"	LEFT JOIN b_release_order t6 ON  t6.id = t.release_order_id                                                                             "
            +"	LEFT JOIN (SELECT t9.NAME warehouse_name,t8.warehouse_id,t7.id FROM b_pp t7                                                             "
            +"			LEFT JOIN b_pp_product t8 ON t7.id = t8.pp_id AND t8.type = 1                                                                   "
            +"			LEFT JOIN m_warehouse t9 ON t9.id = t8.warehouse_id GROUP BY t7.id                                                              "
            +"			) t9 ON t9.id = t.id                                                                                                            "
            +"	LEFT JOIN m_cancel t10 ON t10.serial_id = t.id AND t10.serial_type = '"+ DictConstant.DICT_B_PP+"'                                      "
            +"	LEFT JOIN b_wo_router t11 ON t.router_id = t11.id                                                                                       "
            +"	LEFT JOIN (select t12.pp_id,count(t12.pp_id) bwo_sum from b_wo t12                                                                      "
            +"	where t12.status != '"+DictConstant.DICT_B_WO_STATUS_5 +"' GROUP BY t12.pp_id) t13 on t13.pp_id = t.id                                  "
            +"  LEFT JOIN (SELECT                                                                                                                       "
            +"	    t.pp_id,                                                                                                                            "
            +"	    CONCAT( '[', GROUP_CONCAT( JSON_OBJECT( 'id', t.id,'goods_name',t2.NAME ,'spec',t2.spec,'qty'                                       "
            +"	    ,t.qty,'wo_qty',IFNULL(t6.wo_qty,0),'wo_unclaimed',(IFNULL(t.qty, 0) - IFNULL(t6.wo_qty,0)))), ']') as json_product_list            "
            +"  FROM                                                                                                                                    "
            +"	    b_pp_product t                                                                                                                      "
            +"	LEFT JOIN m_goods_spec t2 ON t2.id = t.sku_id                                                                                           "
            +"	LEFT JOIN (select t3.pp_id,t4.sku_id,SUM(t4.wo_qty) wo_qty from b_wo t3                                                                 "
            +"		LEFT JOIN b_wo_product t4 ON t4.wo_id = t3.id where                                                                                 "
            +"		t3.status IN('"+ DictConstant.DICT_B_WO_STATUS_2+"','"+ DictConstant.DICT_B_WO_STATUS_3+"')  and                                    "
            +"		t3.pp_id is not null GROUP BY t4.sku_id,t3.pp_id) t6                                                                                         "
            +"	    on t6.pp_id = t.pp_id and t6.sku_id = t.sku_id                                                                                      "
            +"	    GROUP BY t.pp_id                                                                                                                    "
            +"	    ) t14 on t14.pp_id = t.id                                                                                                           "
            +"  LEFT JOIN (SELECT                                                                                                                       "
            +"	       t.pp_id, CONCAT( '[', GROUP_CONCAT( JSON_OBJECT( 'id', t.id,'goods_name',t2.NAME ,'pp_router',t.pp_router,'spec',t2.spec,'qty'   "
            +"	       ,t.qty,'wo_qty',IFNULL(t6.wo_qty,0),'wo_unclaimed',(IFNULL(t.qty, 0) - IFNULL(t6.wo_qty,0)))), ']' ) as json_material_list       "
            +" FROM                                                                                                                                     "
            +"	b_pp_material t                                                                                                                         "
            +"	LEFT JOIN m_goods_spec t2 ON t2.id = t.sku_id                                                                                           "
            +"	LEFT JOIN (select t3.pp_id,t4.sku_id,SUM(t4.wo_qty) wo_qty from b_wo t3                                                                 "
            +"		LEFT JOIN b_wo_material t4 ON t4.wo_id = t3.id where                                                                                "
            +"		t3.status IN('"+ DictConstant.DICT_B_WO_STATUS_2+"','"+ DictConstant.DICT_B_WO_STATUS_3+"')  and                                    "
            +"		t3.pp_id is not null  GROUP BY t4.sku_id,t3.pp_id) t6                                                                                        "
            +"	on t6.pp_id = t.pp_id and t6.sku_id = t.sku_id                                                                                          "
            +"	GROUP BY t.pp_id                                                                                                                        "
            +"	) t15 on t15.pp_id = t.id                                                                                                               "
            +"	WHERE                                                                                                                                   "
            +"	TRUE                                                                                                                                    ";


    @Select("<script>"
            + " ${p1.params.dataScopeAnnotation_with} "
            +  comm_select
            +" AND (t.code like concat('%', #{p1.code}, '%') or #{p1.code} is null or #{p1.code} = '')                                                                                                          "
            +" AND (t6.code like concat('%', #{p1.release_order_code}, '%') or #{p1.release_order_code} is null or #{p1.release_order_code} = '')                                                               "
            +" AND (t9.warehouse_id = #{p1.warehouse_id} or #{p1.warehouse_id} is null)                                                                                                                         "
            +" AND (date_format(t.c_time, '%Y-%m-%d') &gt;= date_format(#{p1.start_time,jdbcType=DATE}, '%Y-%m-%d') or #{p1.start_time,jdbcType=DATE} is null)                                                  "
            +" AND (date_format(t.c_time, '%Y-%m-%d') &lt;= date_format(#{p1.over_time,jdbcType=DATE}, '%Y-%m-%d') or #{p1.over_time,jdbcType=DATE} is null)		                                            "
            +" AND (date_format(t.c_time, '%Y-%m-%d') &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')                                                                                             "
            +" AND (concat(t11.name, t11.code) like concat('%', #{p1.router_code}, '%') or #{p1.router_code} is null or #{p1.router_code} = '')                                                                 "
            + "   <if test='p1.status_list != null and p1.status_list.length!=0' >                                                                                                                        "
            + "    and t.status in                                                                                                                                                                        "
            + "        <foreach collection='p1.status_list' item='item' index='index' open='(' separator=',' close=')'>                                                                                     "
            + "         #{item}                                                                                                                                                                           "
            + "        </foreach>                                                                                                                                                                         "
            + "   </if>                                                                                                                                                                                   "
            +"and EXISTS(                                                                                                                                                                                 "
            +" select 1 from b_pp_product sub2 left join m_goods_spec sub1 on sub2.sku_id = sub1.id  where true                                                                                           "
            +"       and (concat(ifnull(sub1.name, ''), '_', ifnull(sub1.code, '')) like concat('%', #{p1.product_goods_name}, '%') or #{p1.product_goods_name} is null or #{p1.product_goods_name} = '') "
            +"       and sub2.pp_id = t.id)                                                                                                                                                               "
            +"and EXISTS(                                                                                                                                                                                 "
            +"  select 1 from b_pp_material sub2 left join m_goods_spec sub1 on sub2.sku_id = sub1.id where true                                                                                          "
            +"        and (concat(ifnull(sub1.name, ''), '_', ifnull(sub1.code, '')) like concat('%', #{p1.material_goods_name}, '%') or #{p1.material_goods_name} is null or #{p1.material_goods_name} ='') "
            +"        and sub2.pp_id = t.id)                                                                                                                                                              "

            //注意 exists里面使用count(1)需要配合分组使用 or 返回 IF(count( 1 )<1,NULL,true)
            + "    <if test='p1.todo_status == 0' >                                                                                                                                                             "
            + "  and exists (                                                                                                                                                                                   "
            + "		SELECT                                                                                                                                                                                      "
            + "				count(1)                                                                                                                                                                                   "
            + "			FROM                                                                                                                                                                                    "
            + "				b_todo subt1                                                                                                                                                                        "
            + "				INNER JOIN v_permission_operation_all subt2 ON subt2.staff_id = #{p1.staff_id,jdbcType=INTEGER}                                                                                     "
            + "				AND subt1.position_id = subt2.position_id                                                                                                                                           "
            + "				AND subt2.operation_perms = subt1.perms                                                                                                                                             "
            + "			WHERE                                                                                                                                                                                   "
            + "				t.id = subt1.serial_id                                                                                                                                                              "
            + "				AND subt1.serial_type = '"+ SystemConstants.SERIAL_TYPE.B_PP+"'                                                                                                                     "
            + "				AND subt1.STATUS = '"+ DictConstant.DICT_B_TODO_STATUS_TODO+"'                                                                                                                      "
            + "				GROUP BY subt1.serial_id, subt1.serial_type                                                                                                                                         "
            + "  )                                                                                                                                                                                              "
            + "      </if>                                                                                                                                                                                      "

            // 已办
            + "      <if test='p1.todo_status == 1' >                                                                                                                                                           "
            + "      and exists (                                                                                                                                                                               "
            + "             select count(1)                                                                                                                                                                           "
            + "               from b_already_do subt1                                                                                                                                                           "
            + "              where subt1.serial_type = '" + SystemConstants.SERIAL_TYPE.B_PP + "'                                                                                                               "
            + "                and subt1.staff_id = #{p1.staff_id,jdbcType=INTEGER}                                                                                                                             "
            + "                and serial_id = t.id                                                                                                                                                             "
            + "				GROUP BY subt1.serial_id, subt1.serial_type                                                                                                                                         "
            + "       )                                                                                                                                                                                         "
            + "      </if>                                                                                                                                                                                      "
            + " ${p1.params.dataScopeAnnotation} "
            + "</script>")
    @Results({
            @Result(property = "material_list", column = "json_material_list", javaType = List.class, typeHandler = JsonArrayTypeHandler.class),
            @Result(property = "product_list", column = "json_product_list", javaType = List.class, typeHandler = JsonArrayTypeHandler.class),
    })
    IPage<BPpVo> selectPageList(@Param("p1") BPpVo bPpVo, Page<BPpVo> pageCondition);


    @Select("SELECT                                                                                                       "
            +"  	t.id,                                                                                                  "
            +"  	t.status,                                                                                              "
            +"  	t.CODE,                                                                                                "
            +"  	t1.label status_name,                                                                                  "
            +"  	t6.code release_order_code,	                                                                           "
            +"  	t11.code router_code,					                                                               "
            +"  	t11.name router_name,					                                                               "
            +"  	t11.id router_id,					                                                                   "
            +"  	t.json_product_list,	                                                                               "
            +"  	t.json_material_list,	                                                                               "
            +"  	t10.remark,					                                                                           "
            +"  	t2.name c_name,                                                                                        "
            +"  	t.c_time,			                                                                                   "
            +"  	t3.name u_name,			                                                                               "
            +"  	t.u_time,				                                                                               "
            +"  	t4.name audit_name,		                                                                               "
            +"  	t.audit_time,	   		                                                                               "
            +"  	t.owner_code,	   		                                                                               "
            +"  	t.owner_id,	   		                                                                                   "
            +"  	t5.name owner_name,	   		                                                                           "
            +"  	t.release_order_id,	   		                                                                           "
            +"  	t.release_order_detail_id,	   		                                                                           "
            +"  	t.plan_time,                                                                                           "
            +"  	t.plan_end_time,                                                                                           "
            +"	t6.type_name release_order_type_name,                                                                      "
            +"	t12.commodity_spec_code release_sku_code,																	"
            +"	t12.commodity_name release_sku_name,																		"
            +"	t13.pm release_pm,																							"
            +"	t12.type_gauge release_type_gauge,																			"
            +"	t12.qty release_qty,																						"
            +"	t12.unit_name release_unit_name,                                                                           "
            +"	t12.commodity_spec release_spec,   																			"
            +"	ifnull(t16.has_product_num, 0) + ifnull(t18.has_product_num, 0) + ifnull(t19.has_product_num, 0) has_product_num 							"
            +"  FROM                                                                                                       "
            +"  	b_pp t                                                                                                 "
            +"  	LEFT JOIN s_dict_data t1 ON t.STATUS = t1.dict_value AND t1.CODE = '"+ DictConstant.DICT_B_PP_STATUS+"'"
            +"  	LEFT JOIN m_staff t2 ON t2.id = t.c_id                                                                 "
            +"  	LEFT JOIN m_staff t3 ON t3.id = t.u_id                                                                 "
            +"  	LEFT JOIN m_staff t4 ON t4.id = t.audit_id                                                             "
            +"  	LEFT JOIN m_owner t5 ON t5.id = t.owner_id                                                             "
            +"  	LEFT JOIN b_release_order t6 ON  t6.id = t.release_order_id                                            "
            +"  	LEFT JOIN (SELECT t9.NAME warehouse_name,t8.warehouse_id,t7.id FROM b_pp t7                            "
            +"  			LEFT JOIN b_pp_product t8 ON t7.id = t8.pp_id AND t8.type = 1                                  "
            +"  			LEFT JOIN m_warehouse t9 ON t9.id = t8.warehouse_id GROUP BY t7.id                             "
            +"  			) t9 ON t9.id = t.id                                                                           "
            +"  	LEFT JOIN m_cancel t10 ON t10.serial_id = t.id AND t10.serial_type = '"+ DictConstant.DICT_B_PP+"'     "
            +"  	LEFT JOIN b_wo_router t11 ON t.router_id = t11.id                                                      "
            +"    LEFT JOIN b_release_order_detail t12 ON t12.release_order_id = t6.id                                     "
            +"		LEFT JOIN m_goods_spec t13 ON t12.commodity_spec_code = t13.code                                       "
            +"		LEFT JOIN (SELECT	t14.delivery_order_detail_id,sum( t15.wo_qty ) has_product_num 	FROM b_wo t14 "
            +"		LEFT JOIN b_wo_product t15 ON t14.id = t15.wo_id 	WHERE (t14.STATUS = '"+ DictConstant.DICT_B_WO_STATUS_3 +"' "
            +"			OR t14.STATUS = '"+ DictConstant.DICT_B_WO_STATUS_2 +"')                                                        "
            +"			AND t15.type = '"+ DictConstant.DICT_B_ROUTER_PRODUCT_TYPE_C +"'                                           "
            +"			GROUP BY t14.delivery_order_detail_id ) t16 ON t16.delivery_order_detail_id = t12.id  AND t16.delivery_order_detail_id IS NOT NULL                   "
            +"		LEFT JOIN (SELECT t14.delivery_order_detail_id,	sum( t15.wo_qty ) has_product_num FROM b_rt_wo t14             "
            +"  	LEFT JOIN b_rt_wo_product t15 ON t14.id = t15.wo_id WHERE 	(t14.STATUS = '"+DictConstant.DICT_B_WO_STATUS_3 +"'"
            +"			OR t14.STATUS = '"+ DictConstant.DICT_B_WO_STATUS_2 +"')                                              "
            +"			AND t15.type = '"+ DictConstant.DICT_B_ROUTER_PRODUCT_TYPE_C +"'                                           "
            +"			GROUP BY	t14.delivery_order_detail_id ) t18                                                             "
            +"			ON t18.delivery_order_detail_id = t12.id AND t18.delivery_order_detail_id IS NOT NULL   "

            +"		LEFT JOIN (SELECT t14.pp_id,sum( t15.wo_qty ) has_product_num FROM b_wo t14 "
            +"		LEFT JOIN b_wo_product t15 ON t14.id = t15.wo_id WHERE (t14.STATUS = '"+ DictConstant.DICT_B_WO_STATUS_3 +"' "
            +"			OR t14.STATUS = '"+ DictConstant.DICT_B_WO_STATUS_2 +"' )                                                       "
            +"			AND t15.type = '"+ DictConstant.DICT_B_ROUTER_PRODUCT_TYPE_C +"'                                           "
            +"			GROUP BY t14.pp_id ) t19 ON t19.pp_id = t.id AND t16.delivery_order_detail_id IS NULL   where t.id = #{id}                 "

//            +"		LEFT JOIN (SELECT t14.delivery_order_detail_id,	sum( t15.wo_qty ) has_product_num FROM b_rt_wo t14             "
//            +"  	LEFT JOIN b_rt_wo_product t15 ON t14.id = t15.wo_id WHERE 	t14.STATUS = '"+DictConstant.DICT_B_WO_STATUS_3 +"'"
//            +"			OR t14.STATUS = '"+ DictConstant.DICT_B_WO_STATUS_2 +"'                                               "
//            +"			AND t15.type = '"+ DictConstant.DICT_B_ROUTER_PRODUCT_TYPE_C +"'                                           "
//            +"			GROUP BY t14.delivery_order_detail_id ) t20                                                             "
//            +"			ON t20.pp_id = t.id AND t18.delivery_order_detail_id IS NOT NULL   "
    )
    BPpVo selectById(Integer id);


    @Select("<script>"
            + " ${p1.params.dataScopeAnnotation_with}                                                "
            +" SELECT                                                                            "
            +"	count(t.id)                                                                           "
            +" FROM                                                                              "
            +"	  b_todo subt1                                                                                     "
            +"	INNER JOIN v_permission_operation_all subt2 ON subt2.staff_id = #{p1.staff_id,jdbcType=INTEGER}  "
            +"	  AND subt1.position_id = subt2.position_id                                                        "
            +"	  AND subt2.operation_perms = subt1.perms                                                          "
            +"	  AND subt1.serial_type = '" + SystemConstants.SERIAL_TYPE.B_PP + "'                               "
            + "	  AND subt1.STATUS = '" + DictConstant.DICT_B_TODO_STATUS_TODO + "'                                "
            +"	INNER JOIN b_pp t ON t.id = subt1.serial_id                                                                       "
            +"	LEFT JOIN b_release_order t6 ON  t6.id = t.release_order_id                     "
            +"	LEFT JOIN (SELECT t9.NAME warehouse_name,t8.warehouse_id,t7.id FROM b_pp t7 "
            +"			LEFT JOIN b_pp_product t8 ON t7.id = t8.pp_id AND t8.type = 1           "
            +"			LEFT JOIN m_warehouse t9 ON t9.id = t8.warehouse_id GROUP BY t7.id      "
            +"			) t9 ON t9.id = t.id                                                    "
            +"  LEFT JOIN b_wo_router t11 ON t.router_id = t11.id                                                      "
            +"	WHERE  TRUE                                                                    "
            +" AND (t.code like concat('%', #{p1.code}, '%') or #{p1.code} is null or #{p1.code} = '')                                                                                                         "
            +" AND (t6.code like concat('%', #{p1.release_order_code}, '%') or #{p1.release_order_code} is null or #{p1.release_order_code} = '')                                                           "
            +" AND (t9.warehouse_id = #{p1.warehouse_id} or #{p1.warehouse_id} is null)                                                                                                                  "
            +" AND (date_format(t.c_time, '%Y-%m-%d') &gt;= date_format(#{p1.start_time,jdbcType=DATE}, '%Y-%m-%d') or #{p1.start_time,jdbcType=DATE} is null)                                                 "
            +" AND (date_format(t.c_time, '%Y-%m-%d') &lt;= date_format(#{p1.over_time,jdbcType=DATE}, '%Y-%m-%d') or #{p1.over_time,jdbcType=DATE} is null)		                                              "
            +" AND (date_format(t.c_time, '%Y-%m-%d') &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')                                                                                            "
            +" AND (concat(t11.name, t11.code) like concat('%', #{p1.router_code}, '%') or #{p1.router_code} is null or #{p1.router_code} = '')                                                                "
            + "   <if test='p1.status_list != null and p1.status_list.length!=0' >                                                                                                                        "
            + "    and t.status in                                                                                                                                                                        "
            + "        <foreach collection='p1.status_list' item='item' index='index' open='(' separator=',' close=')'>                                                                                     "
            + "         #{item}                                                                                                                                                                           "
            + "        </foreach>                                                                                                                                                                         "
            + "   </if>                                                                                                                                                                                   "
            +"and EXISTS(                                                                                                                                                                                 "
            +" select count(1) from b_pp_product sub2 left join m_goods_spec sub1 on sub2.sku_id = sub1.id  where true                                                                                           "
            +"       and (concat(ifnull(sub1.name, ''), '_', ifnull(sub1.code, '')) like concat('%', #{p1.product_goods_name}, '%') or #{p1.product_goods_name} is null or #{p1.product_goods_name} = '') "
            +"       and sub2.pp_id = t.id)                                                                                                                                                               "
            +"and EXISTS(                                                                                                                                                                                 "
            +"  select count(1) from b_pp_material sub2 left join m_goods_spec sub1 on sub2.sku_id = sub1.id where true                                                                                          "
            +"        and (concat(ifnull(sub1.name, ''), '_', ifnull(sub1.code, '')) like concat('%', #{p1.material_goods_name}, '%') or #{p1.material_goods_name} is null or #{p1.material_goods_name} ='') "
            +"        and sub2.pp_id = t.id)                                                                                                                                                              "
            + "  ${p1.params.dataScopeAnnotation}   "
            +"</script>"
    )
    Integer selectTodoCount(@Param("p1") BPpVo bPpVo);



    @Select("<script>"
            + " ${p1.params.dataScopeAnnotation_with}                                                "
            +"SELECT                                                                                                                                                                                      "
            +"	t.id,                                                                                                                                                                                     "
            +"	IFNULL(SUM( t4.qty ),0) product_actual,                                                                                                                                                   "
            +"	IFNULL(SUM( t5.qty ),0) material_actual,                                                                                                                                                  "
            +"	IFNULL(SUM( t6.wo_qty),0) product_actual_wo,                                                                                                                                              "
            +"	IFNULL(SUM( t7.wo_qty),0) material_actual_wo,                                                                                                                                             "
            +"	IFNULL(SUM(t4.qty),0)-IFNULL(SUM(t6.wo_qty),0) product_actual_wait,                                                                                                                       "
            +"	IFNULL(SUM(t5.qty),0)-IFNULL(SUM(t7.wo_qty),0) material_actual_wait                                                                                                                       "
            +"FROM                                                                                                                                                                                        "
            +"	b_pp t                                                                                                                                                                                    "
            +"	LEFT JOIN b_release_order t1 ON t1.id = t.release_order_id                                                                                                                                "
            +"	LEFT JOIN b_wo_router t2 ON t.router_id = t2.id                                                                                                                                           "
            +"	LEFT JOIN (SELECT	t3.NAME warehouse_name,	t2.warehouse_id,t1.id                                                                                                                         "
            +"			FROM	b_pp t1	                                                                                                                                                                  "
            +"			LEFT JOIN b_pp_product t2 ON t1.id = t2.pp_id                                                                                                                                     "
            +"			AND t2.type = 1                                                                                                                                                                   "
            +"			LEFT JOIN m_warehouse t3 ON t3.id = t2.warehouse_id                                                                                                                               "
            +"			GROUP BY	t1.id ) t3 ON t3.id = t.id	                                                                                                                                          "
            +"	LEFT JOIN (SELECT t1.pp_id,SUM( t1.qty ) qty                                                                                                                                              "
            +"			FROM b_pp_product t1                                                                                                                                                              "
            +"			LEFT JOIN b_pp t2 ON t1.pp_id = t2.id                                                                                                                                             "
            +"			GROUP BY t1.pp_id) t4 on t4.pp_id = t.id                                                                                                                                          "
            +"	LEFT JOIN (SELECT t1.pp_id,SUM( t1.qty ) qty                                                                                                                                              "
            +"			FROM b_pp_material t1                                                                                                                                                             "
            +"			LEFT JOIN b_pp t2 ON t1.pp_id = t2.id                                                                                                                                             "
            +"			GROUP BY t1.pp_id) t5 on t5.pp_id = t.id		                                                                                                                                  "
            +"	LEFT JOIN (SELECT t2.pp_id,SUM( t1.wo_qty ) wo_qty                                                                                                                                        "
            +"			FROM b_wo_product t1                                                                                                                                                              "
            +"			LEFT JOIN b_wo t2 ON t1.wo_id = t2.id where t2.status IN('"+ DictConstant.DICT_B_WO_STATUS_2+"','"+ DictConstant.DICT_B_WO_STATUS_3+"')                                                                                   "
            +"			GROUP BY t2.pp_id) t6 on t6.pp_id = t.id                                                                                                                                          "
            +"	LEFT JOIN (SELECT t2.pp_id,SUM( t1.wo_qty ) wo_qty                                                                                                                                        "
            +"			FROM b_wo_material t1                                                                                                                                                             "
            +"			LEFT JOIN b_wo t2 ON t1.wo_id = t2.id  where t2.status IN('"+ DictConstant.DICT_B_WO_STATUS_2+"','"+ DictConstant.DICT_B_WO_STATUS_3+"')                                                                                                                                                 "
            +"			GROUP BY t2.pp_id) t7 on t7.pp_id = t.id                                                                                                                                          "
            +"			WHERE                                                                                                                                                                        "
            +" t.status != '"+ DictConstant.DICT_B_PP_STATUS_CANCEL+"'                                                                                          "
            +" AND (t.code like concat('%', #{p1.code}, '%') or #{p1.code} is null or #{p1.code} = '')                                                                                           "
            +"AND (t1.code like concat('%', #{p1.release_order_code}, '%') or #{p1.release_order_code} is null or #{p1.release_order_code} = '')                                                          "
            +"AND (t3.warehouse_id = #{p1.warehouse_id} or #{p1.warehouse_id} is null)                                                                                                                    "
            +"AND (date_format(t.c_time, '%Y-%m-%d') &gt;= date_format(#{p1.start_time,jdbcType=DATE}, '%Y-%m-%d') or #{p1.start_time,jdbcType=DATE} is null)                                             "
            +"AND (date_format(t.c_time, '%Y-%m-%d') &lt;= date_format(#{p1.over_time,jdbcType=DATE}, '%Y-%m-%d') or #{p1.over_time,jdbcType=DATE} is null)		                                          "
            +"AND (date_format(t.c_time, '%Y-%m-%d') &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')                                                                                        "
            +"AND (concat(t2.name, t2.code) like concat('%', #{p1.router_code}, '%') or #{p1.router_code} is null or #{p1.router_code} = '')                                                              "

            + "   <if test='p1.status_list != null and p1.status_list.length!=0' >                                                                                                                        "
            + "    and t.status in                                                                                                                                                                        "
            + "        <foreach collection='p1.status_list' item='item' index='index' open='(' separator=',' close=')'>                                                                                     "
            + "         #{item}                                                                                                                                                                           "
            + "        </foreach>                                                                                                                                                                         "
            + "   </if>                                                                                                                                                                                   "

            +"and EXISTS(                                                                                                                                                                                 "
            +" select 1 from b_pp_product sub2 left join m_goods_spec sub1 on sub2.sku_id = sub1.id  where true                                                                                           "
            +"       and (concat(ifnull(sub1.name, ''), '_', ifnull(sub1.code, '')) like concat('%', #{p1.product_goods_name}, '%') or #{p1.product_goods_name} is null or #{p1.product_goods_name} = '') "
            +"       and sub2.pp_id = t.id)                                                                                                                                                               "
            +"and EXISTS(                                                                                                                                                                                 "
            +"  select 1 from b_pp_material sub2 left join m_goods_spec sub1 on sub2.sku_id = sub1.id where true                                                                                          "
            +"        and (concat(ifnull(sub1.name, ''), '_', ifnull(sub1.code, '')) like concat('%', #{p1.material_goods_name}, '%') or #{p1.material_goods_name} is null or #{p1.material_goods_name} ='') "
            +"        and sub2.pp_id = t.id)                                                                                                                                                              "
            +"    <if test='p1.todo_status == 0' >                                                                                                                                                        "
            +"  and exists (                                                                                                                                                                              "
            +"		SELECT                                                                                                                                                                                "
            +"				count(1)                                                                                                                                                                             "
            +"			FROM                                                                                                                                                                              "
            +"				b_todo subt1                                                                                                                                                                  "
            +"				INNER JOIN v_permission_operation_all subt2 ON subt2.staff_id = #{p1.staff_id,jdbcType=INTEGER}                                                                               "
            +"				AND subt1.position_id = subt2.position_id                                                                                                                                     "
            +"				AND subt2.operation_perms = subt1.perms                                                                                                                                       "
            +"			WHERE                                                                                                                                                                             "
            +"				t.id = subt1.serial_id                                                                                                                                                        "
            +"				AND subt1.serial_type = '"+ SystemConstants.SERIAL_TYPE.B_PP+"'                                                                                                               "
            +"				AND subt1.STATUS = '"+ DictConstant.DICT_B_TODO_STATUS_TODO+"'                                                                                                                "
            +"				GROUP BY subt1.serial_id, subt1.serial_type                                                                                                                                   "
            +"  )                                                                                                                                                                                         "
            +"      </if>                                                                                                                                                                                 "
            // 已办
            + "      <if test='p1.todo_status == 1' >                                                                                                                                                           "
            + "      and exists (                                                                                                                                                                               "
            + "             select count(1)                                                                                                                                                                           "
            + "               from b_already_do subt1                                                                                                                                                           "
            + "              where subt1.serial_type = '" + SystemConstants.SERIAL_TYPE.B_PP + "'                                                                                                               "
            + "                and subt1.staff_id = #{p1.staff_id,jdbcType=INTEGER}                                                                                                                             "
            + "                and serial_id = t.id                                                                                                                                                             "
            + "				GROUP BY subt1.serial_id, subt1.serial_type                                                                                                                                         "
            + "       )                                                                                                                                                                                         "
            + "      </if>                                                                                                                                                                                      "
            + " ${p1.params.dataScopeAnnotation} "
            + "</script>"
    )
    BPpVo selectListSum(@Param("p1") BPpVo bPpVo);

    @Select("<script>"
            + " ${p1.params.dataScopeAnnotation_with}                                                "
            +" SELECT                                                                            "
            +"	count(t.id)                                                                           "
            +" FROM                                                                              "
            +" b_pp t                                                                        "
            +"	LEFT JOIN b_release_order t6 ON  t6.id = t.release_order_id                     "
            +"	LEFT JOIN (SELECT t9.NAME warehouse_name,t8.warehouse_id,t7.id FROM b_pp t7 "
            +"			LEFT JOIN b_pp_product t8 ON t7.id = t8.pp_id AND t8.type = 1           "
            +"			LEFT JOIN m_warehouse t9 ON t9.id = t8.warehouse_id GROUP BY t7.id      "
            +"			) t9 ON t9.id = t.id                                                    "
            +"  LEFT JOIN b_wo_router t11 ON t.router_id = t11.id                                                      "
            +"	WHERE  TRUE                                                                    "
            +" AND (t.code like concat('%', #{p1.code}, '%') or #{p1.code} is null or #{p1.code} = '')                                                                                                         "
            +" AND (t6.code like concat('%', #{p1.release_order_code}, '%') or #{p1.release_order_code} is null or #{p1.release_order_code} = '')                                                           "
            +" AND (t9.warehouse_id = #{p1.warehouse_id} or #{p1.warehouse_id} is null)                                                                                                                  "
            +" AND (date_format(t.c_time, '%Y-%m-%d') &gt;= date_format(#{p1.start_time,jdbcType=DATE}, '%Y-%m-%d') or #{p1.start_time,jdbcType=DATE} is null)                                                 "
            +" AND (date_format(t.c_time, '%Y-%m-%d') &lt;= date_format(#{p1.over_time,jdbcType=DATE}, '%Y-%m-%d') or #{p1.over_time,jdbcType=DATE} is null)		                                              "
            +" AND (date_format(t.c_time, '%Y-%m-%d') &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')                                                                                            "
            +" AND (concat(t11.name, t11.code) like concat('%', #{p1.router_code}, '%') or #{p1.router_code} is null or #{p1.router_code} = '')                                                                "
            + "   <if test='p1.status_list != null and p1.status_list.length!=0' >                                                                                                                        "
            + "    and t.status in                                                                                                                                                                        "
            + "        <foreach collection='p1.status_list' item='item' index='index' open='(' separator=',' close=')'>                                                                                     "
            + "         #{item}                                                                                                                                                                           "
            + "        </foreach>                                                                                                                                                                         "
            + "   </if>                                                                                                                                                                                   "
            +"and EXISTS(                                                                                                                                                                                 "
            +" select count(1) from b_pp_product sub2 left join m_goods_spec sub1 on sub2.sku_id = sub1.id  where true                                                                                           "
            +"       and (concat(ifnull(sub1.name, ''), '_', ifnull(sub1.code, '')) like concat('%', #{p1.product_goods_name}, '%') or #{p1.product_goods_name} is null or #{p1.product_goods_name} = '') "
            +"       and sub2.pp_id = t.id)                                                                                                                                                               "
            +"and EXISTS(                                                                                                                                                                                 "
            +"  select count(1) from b_pp_material sub2 left join m_goods_spec sub1 on sub2.sku_id = sub1.id where true                                                                                          "
            +"        and (concat(ifnull(sub1.name, ''), '_', ifnull(sub1.code, '')) like concat('%', #{p1.material_goods_name}, '%') or #{p1.material_goods_name} is null or #{p1.material_goods_name} ='') "
            +"        and sub2.pp_id = t.id)                                                                                                                                                              "
            + "  ${p1.params.dataScopeAnnotation}   "
            +"</script>"
    )
    Long selectExportCount(@Param("p1") BPpVo bPpVo);

    @Select("<script>"
            +" ${p1.params.dataScopeAnnotation_with}                                                                                                    "
            +"SELECT                                                                                                                                    "
            +"  @row_num:= @row_num+ 1 as no,                                                                                                           "
            +"	t.id,                                                                                                                                   "
            +"	t.status,                                                                                                                               "
            +"	t.CODE,                                                                                                                                 "
            +"	t1.label status_name,                                                                                                                   "
            +"	t6.code release_order_code,	                                                                                                            "
            +"	t11.code router_code,					                                                                                                "
            +"	t11.name router_name,					                                                                                                "
            +"	t11.id router_id,					                                                                                                    "
            +"	t14.json_product_list,	                                                                                                                "
            +"	t15.json_material_list,	                                                                                                                "
            +"	t10.remark,					                                                                                                            "
            +"	t2.name c_name,                                                                                                                         "
            +"	t.c_time,			                                                                                                                    "
            +"	t3.name u_name,			                                                                                                                "
            +"	t.u_time,				                                                                                                                "
            +"	t4.name audit_name,		                                                                                                                "
            +"	t.audit_time,	   		                                                                                                                "
            +"	t.owner_code,	   		                                                                                                                "
            +"	t.owner_id,	   		                                                                                                                    "
            +"	t5.name owner_name,	   		                                                                                                            "
            +"	t.release_order_id,	   		                                                                                                            "
            +"	t.plan_time,	   		                                                                                                                "
            +"	ifnull(t13.bwo_sum, 0) bwo_sum,   		                                                                                                "
            +"	t9.warehouse_name	   		                                                                                                "
            +"FROM                                                                                                                                      "
            +"	b_pp t                                                                                                                                  "
            +"	LEFT JOIN s_dict_data t1 ON t.STATUS = t1.dict_value AND t1.CODE = '"+ DictConstant.DICT_B_PP_STATUS+"'                                 "
            +"	LEFT JOIN m_staff t2 ON t2.id = t.c_id                                                                                                  "
            +"	LEFT JOIN m_staff t3 ON t3.id = t.u_id                                                                                                  "
            +"	LEFT JOIN m_staff t4 ON t4.id = t.audit_id                                                                                              "
            +"	LEFT JOIN m_owner t5 ON t5.id = t.owner_id                                                                                              "
            +"	LEFT JOIN b_release_order t6 ON  t6.id = t.release_order_id                                                                             "
            +"	LEFT JOIN (SELECT t9.NAME warehouse_name,t8.warehouse_id,t7.id FROM b_pp t7                                                             "
            +"			LEFT JOIN b_pp_product t8 ON t7.id = t8.pp_id AND t8.type = 1                                                                   "
            +"			LEFT JOIN m_warehouse t9 ON t9.id = t8.warehouse_id GROUP BY t7.id                                                              "
            +"			) t9 ON t9.id = t.id                                                                                                            "
            +"	LEFT JOIN m_cancel t10 ON t10.serial_id = t.id AND t10.serial_type = '"+ DictConstant.DICT_B_PP+"'                                      "
            +"	LEFT JOIN b_wo_router t11 ON t.router_id = t11.id                                                                                       "
            +"	LEFT JOIN (select t12.pp_id,count(t12.pp_id) bwo_sum from b_wo t12                                                                      "
            +"	where t12.status IN ('"+ DictConstant.DICT_B_WO_STATUS_2+"','"+ DictConstant.DICT_B_WO_STATUS_3+"') GROUP BY t12.pp_id) t13 on t13.pp_id = t.id                                  "
            +"  LEFT JOIN (SELECT                                                                                                                       "
            +"	    t.pp_id,                                                                                                                            "
            +"	    CONCAT( '[', GROUP_CONCAT( JSON_OBJECT( 'id', t.id,'goods_name',t2.NAME ,'spec',t2.spec,'qty'                                       "
            +"	    ,t.qty,'wo_qty',IFNULL(t6.wo_qty,0),'wo_unclaimed',(IFNULL(t.qty, 0) - IFNULL(t6.wo_qty,0)))), ']') as json_product_list            "
            +"  FROM                                                                                                                                    "
            +"	    b_pp_product t                                                                                                                      "
            +"	LEFT JOIN m_goods_spec t2 ON t2.id = t.sku_id                                                                                           "
            +"	LEFT JOIN (select t3.pp_id,t4.sku_id,SUM(t4.wo_qty) wo_qty from b_wo t3                                                                 "
            +"		LEFT JOIN b_wo_product t4 ON t4.wo_id = t3.id where                                                                                 "
            +"		t3.status IN('"+ DictConstant.DICT_B_WO_STATUS_2+"','"+ DictConstant.DICT_B_WO_STATUS_3+"')  and                                    "
            +"		t3.pp_id is not null GROUP BY t4.sku_id,t3.pp_id) t6                                                                                         "
            +"	    on t6.pp_id = t.pp_id and t6.sku_id = t.sku_id                                                                                      "
            +"	    GROUP BY t.pp_id                                                                                                                    "
            +"	    ) t14 on t14.pp_id = t.id                                                                                                           "
            +"  LEFT JOIN (SELECT                                                                                                                       "
            +"	       t.pp_id, CONCAT( '[', GROUP_CONCAT( JSON_OBJECT( 'id', t.id,'goods_name',t2.NAME ,'pp_router',t.pp_router,'spec',t2.spec,'qty'   "
            +"	       ,t.qty,'wo_qty',IFNULL(t6.wo_qty,0),'wo_unclaimed',(IFNULL(t.qty, 0) - IFNULL(t6.wo_qty,0)))), ']' ) as json_material_list       "
            +" FROM                                                                                                                                     "
            +"	b_pp_material t                                                                                                                         "
            +"	LEFT JOIN m_goods_spec t2 ON t2.id = t.sku_id                                                                                           "
            +"	LEFT JOIN (select t3.pp_id,t4.sku_id,SUM(t4.wo_qty) wo_qty from b_wo t3                                                                 "
            +"		LEFT JOIN b_wo_material t4 ON t4.wo_id = t3.id where                                                                                "
            +"		t3.status IN('"+ DictConstant.DICT_B_WO_STATUS_2+"','"+ DictConstant.DICT_B_WO_STATUS_3+"')  and                                    "
            +"		t3.pp_id is not null  GROUP BY t4.sku_id,t3.pp_id) t6                                                                                        "
            +"	on t6.pp_id = t.pp_id and t6.sku_id = t.sku_id                                                                                          "
            +"	GROUP BY t.pp_id                                                                                                                        "
            +"	) t15 on t15.pp_id = t.id                                                                                                               "
            +"   ,(select @row_num:=0) t20                                                                                                            "
            +"	WHERE                                                                                                                                   "
            +"	TRUE                                                                                                                                    "
            +" AND (t.code like concat('%', #{p1.code}, '%') or #{p1.code} is null or #{p1.code} = '')                                                                                                         "
            +" AND (t6.code like concat('%', #{p1.release_order_code}, '%') or #{p1.release_order_code} is null or #{p1.release_order_code} = '')                                                           "
            +" AND (t9.warehouse_id = #{p1.warehouse_id} or #{p1.warehouse_id} is null)                                                                                                                  "
            +" AND (date_format(t.c_time, '%Y-%m-%d') &gt;= date_format(#{p1.start_time,jdbcType=DATE}, '%Y-%m-%d') or #{p1.start_time,jdbcType=DATE} is null)                                                 "
            +" AND (date_format(t.c_time, '%Y-%m-%d') &lt;= date_format(#{p1.over_time,jdbcType=DATE}, '%Y-%m-%d') or #{p1.over_time,jdbcType=DATE} is null)		                                              "
            +" AND (date_format(t.c_time, '%Y-%m-%d') &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')                                                                                            "
            +" AND (concat(t11.name, t11.code) like concat('%', #{p1.router_code}, '%') or #{p1.router_code} is null or #{p1.router_code} = '')                                                                "
            + "   <if test='p1.status_list != null and p1.status_list.length!=0' >                                                                                                                        "
            + "    and t.status in                                                                                                                                                                        "
            + "        <foreach collection='p1.status_list' item='item' index='index' open='(' separator=',' close=')'>                                                                                     "
            + "         #{item}                                                                                                                                                                           "
            + "        </foreach>                                                                                                                                                                         "
            + "   </if>                                                                                                                                                                                   "
            +"and EXISTS(                                                                                                                                                                                 "
            +" select count(1) from b_pp_product sub2 left join m_goods_spec sub1 on sub2.sku_id = sub1.id  where true                                                                                           "
            +"       and (concat(ifnull(sub1.name, ''), '_', ifnull(sub1.code, '')) like concat('%', #{p1.product_goods_name}, '%') or #{p1.product_goods_name} is null or #{p1.product_goods_name} = '') "
            +"       and sub2.pp_id = t.id)                                                                                                                                                               "
            +"and EXISTS(                                                                                                                                                                                 "
            +"  select count(1) from b_pp_material sub2 left join m_goods_spec sub1 on sub2.sku_id = sub1.id where true                                                                                          "
            +"        and (concat(ifnull(sub1.name, ''), '_', ifnull(sub1.code, '')) like concat('%', #{p1.material_goods_name}, '%') or #{p1.material_goods_name} is null or #{p1.material_goods_name} ='') "
            +"        and sub2.pp_id = t.id)                                                                                                                                                              "
            + "   <if test='p1.ids != null and p1.ids.length != 0' >                                                    "
            + "    and t.id in                                                                                          "
            + "        <foreach collection='p1.ids' item='item' index='index' open='(' separator=',' close=')'>         "
            + "         #{item}                                                                                         "
            + "        </foreach>                                                                                       "
            + "   </if>                                                                                                 "
            + "  ${p1.params.dataScopeAnnotation}   "
            +"</script>")
//    @Results({
//            @Result(property = "material_list", column = "json_material_list", javaType = List.class, typeHandler = JsonArrayTypeHandler.class),
//            @Result(property = "product_list", column = "json_product_list", javaType = List.class, typeHandler = JsonArrayTypeHandler.class),
//    })
    List<BPpVo> exportList(@Param("p1") BPpVo bPpVo);
}
